package databases;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;

import logParse.ServerStatus;
import tools.*;

public class DBConnection {

	
	/**
	 * 连接数据库
	 */
	public static void Connect() {
		try {
			Class.forName(driver).newInstance();
			con = DriverManager.getConnection(connStr, user, pwd);
			stm = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE ,
									ResultSet.CONCUR_READ_ONLY);
			//验证是否连接成功
			if(!con.isClosed())
		         System.out.println("Succeeded connecting to the Database!");     
			
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println("JDBC驱动未找到！"+driver);
			e.printStackTrace();
		}
	}
		
	/**
	 * 关闭与数据库的连接
	 */
	public static void close() {
		try {
		
			stm.close();
			con.close();
			stm = null;
			con = null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 执行SQL语句，得到结果集
	 * @param sqlStr SQL语句
	 * @return 返回查询结果集
	 * @throws SQLException
	 */
	public static ResultSet getResultSet(String sqlStr) throws SQLException {
		
		if (stm.execute(sqlStr)) {
			rst = stm.getResultSet();
		}

		return rst;
	}
			
	/**
	 * 得到数据库中指定host的最新记录的时间
	 * @return 返回java.util.date 对象
	 * @throws SQLException 
	 * @throws ParseException 
	 */
	public static java.util.Date serchLastestDate(String hostName) throws SQLException, ParseException {

		String SQLStr = Constants.SQL_SerchLastestTime;
		SQLStr = SQLStr.replaceFirst("\\?", Constants.Field_time);
		SQLStr = SQLStr.replaceFirst("\\?", Constants.Table_ServerLog);
		SQLStr = SQLStr.replaceFirst("\\?", Constants.Field_host);
		SQLStr = SQLStr.replaceFirst("\\?", hostName);
		SQLStr = SQLStr.replaceFirst("\\?", Constants.Field_time);
		rst = getResultSet(SQLStr);
		//若结果集为空，则说明是第一次运行，数据库记录为空
		if (!rst.next()) {
			return null;
		}
		rst.first();
		Timestamp lastestDate =  rst.getTimestamp(1);
		String dateStr = new SimpleDateFormat(Constants.Time_template_MIN).format(lastestDate);
		java.util.Date date = Constants.StringtoDate(dateStr, Constants.Time_template_MIN);
		return date;
	}
	
	
	/**
	 * 将数据插入数据库
	 * @param host 主机名
	 * @param time 时间
	 * @param status 服务状态
	 * @param tableName 插入的数据库表名
	 * @throws SQLException
	 */
	public static void Insert2DB(String host, String time, ServerStatus status, String tableName) 
	throws SQLException {

		String serverName = status.getSrvname();
		int ok = status.getOk();
		int error = status.getError();
		int timeout = status.getTimeout();
	
		String SQLStr = Constants.SQL_InsertTable;
		SQLStr = SQLStr.replaceFirst("\\?", tableName);
		SQLStr = SQLStr.replaceFirst("\\?", Constants.Field_host);
		SQLStr = SQLStr.replaceFirst("\\?", Constants.Field_time);
		SQLStr = SQLStr.replaceFirst("\\?", Constants.Field_ServerName);
		SQLStr = SQLStr.replaceFirst("\\?", Constants.Field_sOk);
		SQLStr = SQLStr.replaceFirst("\\?", Constants.Field_sError);
		SQLStr = SQLStr.replaceFirst("\\?", Constants.Field_sTimeout);
		
		SQLStr = SQLStr.replaceFirst("\\?", host);
		SQLStr = SQLStr.replaceFirst("\\?", time);
		SQLStr = SQLStr.replaceFirst("\\?", serverName);
		SQLStr = SQLStr.replaceFirst("\\?", ok+"");
		SQLStr = SQLStr.replaceFirst("\\?", error+"");
		SQLStr = SQLStr.replaceFirst("\\?", timeout+"");
				
		stm.executeUpdate(SQLStr);
	}
	
	/**
	 * 得到指定主机，指定服务的最新一条记录
	 * @param hostName 指定主机
	 * @param serverName 指定服务名
	 * @return 返回包含查询结果的Map集合
	 * @throws SQLException
	 */
	public static Map<String, String> getLastestData(String hostName, String serverName) throws SQLException {
		Map<String, String> totalData = new HashMap<String, String>();
		String SQLStr = Constants.SQL_SerchLastestData;
		SQLStr = SQLStr.replaceFirst("\\?", hostName);
		SQLStr = SQLStr.replaceFirst("\\?", serverName);
		rst = getResultSet(SQLStr);
		//若结果集为空，则说明数据库中无符合条件的记录
		if (!rst.next()) {
			return null;
		}
		rst.first();
		Timestamp DBDate =  rst.getTimestamp(1);
		String dateStr = new SimpleDateFormat(Constants.Time_template_MIN).format(DBDate);
		String sOK = rst.getInt(2)+"";
		String sERROR = rst.getInt(3)+"";
		String sTIMEOUT = rst.getInt(4)+"";
		
		totalData.put(Constants.Field_time, dateStr);
		totalData.put(Constants.Field_sOk, sOK);
		totalData.put(Constants.Field_sError, sERROR);
		totalData.put(Constants.Field_sTimeout, sTIMEOUT);
		return totalData;
		
	}
	
	
	/**
	 * 得到距今指定时间内的服务状态统计数据
	 * @param hostName 指定主机
	 * @param num	指定距今时间的数量
	 * @param timeUnits	指定距今时间的单位 （只能为 MINUTE、HOUR、DAY、WEEK、MONTH、YEAR）
	 * @return	返回包含查询结果的Map集合
	 * @throws SQLException 
	 * @throws ParseException 
	 */
	public static Map<String, String> getHistoryData(String hostName, int num, String timeUnits ) throws SQLException, ParseException {
		String[] unitsStr = {"MINUTE","HOUR","DAY","WEEK","MONTH","YEAR"};
		boolean isCorrectUnit = false;
		for (String string : unitsStr) {
			if (timeUnits.equalsIgnoreCase(string)) {
				isCorrectUnit = true;
				break;
			}			
		}
		if (num < 0 || !isCorrectUnit) {
			throw new SQLException("方法参数不合法！");
		}
			
		java.util.Date lastestDate = serchLastestDate(hostName);
		if (lastestDate == null ) {
			throw new SQLException("数据库无记录，无法查询！");
		}
		Map<String, String> totalData = new HashMap<String, String>();
		String dateStr = Constants.DatetoString(lastestDate, Constants.Time_template_MIN);
		String SQLStr = Constants.SQL_SerchLastMinute;
		SQLStr = SQLStr.replaceFirst("\\?", hostName);
		SQLStr = SQLStr.replaceFirst("\\?", dateStr);
		SQLStr = SQLStr.replaceFirst("\\?", num+"");
		SQLStr = SQLStr.replaceFirst("\\?", timeUnits);
		rst = getResultSet(SQLStr);
		//若结果集为空，则说明数据库中无符合条件的记录
		if (!rst.next()) {
			return null;
		}
		rst.first();
		Timestamp DBDate =  rst.getTimestamp(1);
		String dStr = new SimpleDateFormat(Constants.Time_template_MIN).format(DBDate);
		String sOK = rst.getInt(2)+"";
		String avgOK = rst.getDouble(3)+"";
		String sERROR = rst.getInt(4)+"";
		String avgERROR = rst.getDouble(5)+"";
		String sTIMEOUT = rst.getInt(6)+"";
		String avgTIMEOUT = rst.getDouble(7)+"";
				
		totalData.put(Constants.Field_time, dStr);
		totalData.put(Constants.Field_sOk, sOK);
		totalData.put(Constants.Field_AVGOk, avgOK);
		totalData.put(Constants.Field_sError, sERROR);
		totalData.put(Constants.Field_AVGError, avgERROR);
		totalData.put(Constants.Field_sTimeout, sTIMEOUT);
		totalData.put(Constants.Field_AVGTimeout, avgTIMEOUT);
			
		return totalData;		
	}
		
	/**
	 * 得到指定时间范围内，指定服务的统计数据，若不指定服务，则为所有服务总和
	 * @param hostName 指定主机
	 * @param begin	时间范围起点
	 * @param end 时间范围终点
	 * @param server 指定服务名
	 * @return 返回包含查询结果的Map集合
	 * @throws SQLException 
	 */
	public static Map<String, String> getTimeRangeData(String hostName, String begin,
											String end, String server) throws SQLException {
		String SQLStr;
		//若未指定server，则查询统计所有服务数据
		if (server == null || server.equals("") || server.equals("All")) {
			SQLStr = Constants.SQL_SerchTimeRange;
			SQLStr = SQLStr.replaceFirst("\\?", hostName);
			SQLStr = SQLStr.replaceFirst("\\?", begin);
			SQLStr = SQLStr.replaceFirst("\\?", end);
		} else { // 否则增加查询server字段
			SQLStr = Constants.SQL_SerchTimeRange_Server;
			SQLStr = SQLStr.replaceFirst("\\?", hostName);
			SQLStr = SQLStr.replaceFirst("\\?", begin);
			SQLStr = SQLStr.replaceFirst("\\?", end);
			SQLStr = SQLStr.replaceFirst("\\?", server);
		}
		rst = getResultSet(SQLStr);
		//若结果集为空，则说明数据库中无符合条件的记录
		if (!rst.next()) {
			return null;
		}
		rst.first();
		Map<String, String> totalData = new HashMap<String, String>();
		String sOK = rst.getInt(1)+"";
		String avgOK = rst.getDouble(2)+"";
		String sERROR = rst.getInt(3)+"";
		String avgERROR = rst.getDouble(4)+"";
		String sTIMEOUT = rst.getInt(5)+"";
		String avgTIMEOUT = rst.getDouble(6)+"";
		
		totalData.put(Constants.Field_sOk, sOK);
		totalData.put(Constants.Field_AVGOk, avgOK);
		totalData.put(Constants.Field_sError, sERROR);
		totalData.put(Constants.Field_AVGError, avgERROR);
		totalData.put(Constants.Field_sTimeout, sTIMEOUT);
		totalData.put(Constants.Field_AVGTimeout, avgTIMEOUT);
		return totalData;		
	}
	
	/**
	 * 得到指定主机所有服务的列表
	 * @param hostName 指定主机
	 * @return 
	 * @throws SQLException 
	 */
	public static String[] getServerList(String hostName) throws SQLException {
		String SQLStr = Constants.SQL_SerchFieldList;
		SQLStr = SQLStr.replaceFirst("\\?", Constants.Field_ServerName);
		SQLStr = SQLStr.replaceFirst("\\?", hostName);
		rst = getResultSet(SQLStr);
		//若结果集为空，则说明数据库中无符合条件的记录
		if (!rst.next()) {
			return null;
		}
		rst.last();
		int rowCount = rst.getRow();
		String[] serverList = new String[rowCount];
		rst.first();
		for (int i = 0; i < serverList.length; i++, rst.next()) {
			serverList[i] = rst.getString(1);
		}
		
		return serverList;
	}
	
	/**
	 * 删除指定主机名下的所有记录。
	 * @param hostName
	 * @throws SQLException
	 */
	public static void deleteData(String hostName) throws SQLException {
		String SQLStr = Constants.SQL_DeleteData;
		SQLStr = SQLStr.replaceFirst("\\?", hostName);
		stm.executeUpdate(SQLStr);		
	}
	
	
	

	public static Connection getCon() {
		return con;
	}

	public static void setCon(Connection con) {
		DBConnection.con = con;
	}

	public static Statement getStm() {
		return stm;
	}

	public static void setStm(Statement stm) {
		DBConnection.stm = stm;
	}




	private static String driver;
	private static String connStr;
	private static String user;
	private static String pwd;
	private static Connection con = null;
	private static Statement stm = null;
	private static ResultSet rst = null; 
	
	static {
		//读取配置文件信息
		Configuration rc = new Configuration("./config/properties.xml");
		driver = rc.getValue("DBdriver").trim();
		connStr = rc.getValue("DBconn").trim();
		user = rc.getValue("DBuser").trim();
		pwd = rc.getValue("DBpwd").trim();
			System.out.println("DB 配置");
		Connect();
		
	}
	

	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		try {
			String[] aa = getServerList("665");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
